import os
import pandas as pd

def merge_excel_files(folder_path, output_file):
    all_data = []

    for file_name in os.listdir(folder_path):
        if file_name.endswith(".xlsx") or file_name.endswith(".xls"):
            file_path = os.path.join(folder_path, file_name)
            df = pd.read_excel(file_path)
            all_data.append(df)

    combined_data = pd.concat(all_data, ignore_index=True)

    # 处理输出文件名存在的情况
    file_exists = os.path.isfile(output_file)
    if file_exists:
        base_name, extension = os.path.splitext(output_file)
        count = 1
        while file_exists:
            new_output_file = f"{base_name}({count}){extension}"
            file_exists = os.path.isfile(new_output_file)
            count += 1
        output_file = new_output_file

    combined_data.to_excel(output_file, index=False)
    print("成功合并所有Excel文件至：", output_file)

# 指定要合并的文件夹路径
folder_path = "指定路径"

# 指定输出文件名
output_file = "combined.xlsx"

# 执行合并操作
merge_excel_files(folder_path, output_file)
